package Professor;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class College_ProfessorDAO implements College_ProfessorIDAO {

	private String DRV ="oracle.jdbc.driver.OracleDriver";
	private String URL ="jdbc:oracle:thin:spirit/spirit@112.216.253.125:1521:xe";
	private String insertSQL ="insert into professor_vo values(college.nextval,college1.nextval,?,?,?,?,?,?,?)";
	private String selectSQL ="select * from professor_vo order by PROF_TR desc";
	private String updateSQL ="update professor_vo set USER_PW = ?,PROF_TEL = ?, PROF_ADDR = ?, SUB_NO = ? where PROF_TR = ?";
	private String deleteSQL ="delete professor_vo where PROF_TR = ?";
	
	@Override
	public boolean insertCollege(College_ProfessorVO p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = this.openDatebase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, p.getUSER_PW());
			pstmt.setString(2, p.getPROF_NAME());
			pstmt.setString(3, p.getPROF_TEL());
			pstmt.setString(4, p.getPROF_ADDR());
			pstmt.setString(5, p.getPROF_OFFICE());
			pstmt.setString(6, p.getDEPT_NO());
			pstmt.setString(7, p.getSUB_NO());	
			
			pstmt.executeUpdate();
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatebase(conn,pstmt,null);
		}
		return result;
	}
	private Connection openDatebase(){
		try{
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
			}catch(Exception ex){
				ex.printStackTrace();
			}
		return null;
	}
	private void closeDatebase(
			Connection c, PreparedStatement p, ResultSet r){
		if(r != null)try{r.close(); r = null;}catch(Exception e){}
		if(p != null)try{p.close(); p = null;}catch(Exception e){}
		if(c != null)try{c.close(); c = null;}catch(Exception e){}
	}
	@Override
	public List<College_ProfessorVO> selectCollege() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<College_ProfessorVO> result = new ArrayList<>();
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				College_ProfessorVO p = new College_ProfessorVO();
				p.setPROF_TR(rs.getString("PROF_TR"));
				p.setPROF_NO(rs.getString("PROF_NO"));
				p.setUSER_PW(rs.getString("USER_PW"));
				p.setPROF_NAME(rs.getString("PROF_NAME"));
				p.setPROF_TEL(rs.getString("PROF_TEL"));
				p.setPROF_ADDR(rs.getString("PROF_ADDR"));
				p.setPROF_OFFICE(rs.getString("PROF_OFFICE"));
				p.setDEPT_NO(rs.getString("DEPT_NO"));
				p.setSUB_NO(rs.getString("SUB_NO"));
				
				result.add(p);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatebase(conn,pstmt,rs);
		}
		return result;
	}

	@Override
	public boolean updateCollege(College_ProfessorVO p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setString(1, p.getUSER_PW());
			pstmt.setString(2, p.getPROF_TEL());
			pstmt.setString(3, p.getPROF_ADDR());
			pstmt.setString(4, p.getSUB_NO());	
			pstmt.setString(5, p.getPROF_TR());
			int cnt = pstmt.executeUpdate();
			if(cnt == 0)
			System.out.println("수정한 데이터가 없습니다");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatebase(conn,pstmt,null);
		}
		return result;
		}

	@Override
	public boolean deleteCollege(College_ProfessorVO p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1, p.getPROF_TR());
			int cnt = pstmt.executeUpdate();
			if(cnt == 0)
				System.out.println("삭제된 데이터가 없습니다.");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatebase(conn,pstmt,null);
		}
		return result;
	}

}
